import java.io.File;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Scanner;
import java.util.regex.Pattern;

import javax.swing.JFrame;

import schema.*;


public class DBManager {
   private String dbName;
   private Connection conn;
   public DBManager(String dbName) {
      this.dbName=dbName;
      try {
         conn=DriverManager.getConnection("jdbc:derby:"+dbName+";create=true");
      } catch (SQLException e) {
         e.printStackTrace();
      }      
   }

   public void createTable(Table table) throws SQLException {
      //[ prepare sql stmt
      final List<Column> primaryColumns=new ArrayList<Column>();
      final Printer printer=new Printer();
      //printer.p("create table ?.? (", dbName, table.getName());
      printer.p("create table ? (", table.getName());
      
      for(int i=0; i<table.getColumns().size(); i++) {
         final Column c=table.getColumns().get(i);
         if(c.isPrimary()) primaryColumns.add(c);
         printer.p("? ? ?", c.getName(), c.getSqlType(), c.isNecessary()?"NOT NULL":"");
         if(i<table.getColumns().size()-1) printer.p(", ");
      }
      if(!primaryColumns.isEmpty()) {
         printer.p(", PRIMARY KEY(");
         for(int i=0; i<primaryColumns.size(); i++) {
            printer.p(primaryColumns.get(i).getName());
            if(i<primaryColumns.size()-1) printer.p(", ");
         }
         printer.p(")");
      }
      printer.p(")");
      System.out.println(printer.toString());
      
      //execute sql stmt
      Statement stmt = null;
      try {
         stmt = conn.createStatement();
         stmt.executeUpdate(printer.toString());
      } catch (SQLException e) {
         e.printStackTrace();
      } finally {
         if (stmt != null) {
            stmt.close();
         }
      }
   }
   
   public ResultSet query(String sqlStmt) {
      ResultSet rs=null;
      try {
         final Statement stmt= conn.createStatement(
               ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE);
         stmt.execute(sqlStmt);
         rs=stmt.getResultSet();
      } catch (SQLException e) {
         e.printStackTrace();
      }
      
      return rs;
   }
   public static void printResultSet(ResultSet resultSet) throws SQLException {
      final ResultSetMetaData rsmd=resultSet.getMetaData();
      int columns = rsmd.getColumnCount();
      System.out.println("=====================");
      for (int i = 1; i <= columns; i++) {
         int jdbcType = rsmd.getColumnType(i);
         String name = rsmd.getColumnTypeName(i);
         System.out.print("Column " + i + " is JDBC type " + jdbcType);
         System.out.println(", which the DBMS calls " + name);
      }
      while(resultSet.next()) {
         for(int i=1; i<=columns; i++) {
            System.out.print(resultSet.getString(i));
            System.out.print(" ");
         }
         System.out.println();
      }
   }
   
   
   //=====================
   public static void testCreateTable() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      final Table t=new Table("STUDENT", "graduate students");
      final Column id=new IntColumn("ID", "id number", true, true);
      final Column name=new StringColumn("NAME", "real name", true, false);
      t.getColumns().add(id);
      t.getColumns().add(name);
      mgr.createTable(t);
   }
   public static void testInsert() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      mgr.query("insert into STUDENT values(1, 'ken')");
   }
   public static void testSelect() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      final ResultSet res=mgr.query("select * from STUDENT");
      printResultSet(res);
   }
   public static void testInsertThroughResultSet() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      final ResultSet res=mgr.query("select * from STUDENT");
      printResultSet(res);
      res.moveToInsertRow();
      res.updateInt(1, 10);
      res.updateString(2, "vivian");
      res.insertRow();
      
      final ResultSet newRes=mgr.query("select * from STUDENT");
      printResultSet(newRes);
   }
   public static void testUpdateThroughResultSet() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      final ResultSet res=mgr.query("select * from STUDENT");
      printResultSet(res);
      res.absolute(1); //res.next() //:failed
      res.updateInt(1, 20);
      res.updateString(2, "Tom");
      res.updateRow();
      
      final ResultSet newRes=mgr.query("select * from STUDENT");
      printResultSet(newRes);
   }
   public static void testDeleteThroughResultSet() throws SQLException {
      final DBManager mgr=new DBManager("SAMPLE");
      final ResultSet res=mgr.query("select * from STUDENT");
      printResultSet(res);
      res.absolute(1); //res.next() //:failed
      res.deleteRow();
      
      final ResultSet newRes=mgr.query("select * from STUDENT");
      printResultSet(newRes);
   }
   

   public static void main(String[] args) throws SQLException, IOException {
      //testCreateTable();
      //testInsert();
      //testSelect();
      //testInsertThroughResultSet();
      //testUpdateThroughResultSet();
      //testDeleteThroughResultSet();
 
   }

}